<?php
/**
 * User: fuyuan.shuai
 * Date: 2015/3/13
 * Time: 10:27
 */

/**
 * Class nl_sql_builder
 * 仅仅提供给后台使用，前端因为涉及到SQL的排序和性能，不建议使用该类
 */

class np_sql_builder
{
    private static $table_field_maps = array();

    private  $_where = array();

    private  $_orderby=array();

    private  $_like=array();

    private $_groupby = array();

    private $_custom_where = array();

    private  $_table_name="";

    private $_limit=array();

    private $_compare = array();

    const NP_PARAMS_VALID_METHOD_INSERT="INSERT";
    const NP_PARAMS_VALID_METHOD_MODIFY="MODIFY";
    const NP_PARAMS_VALID_METHOD_QUERY="QUERY";


    const NP_PARAMS_NULL = "<<NULL>>";

    private $errors = array();

    /**
     * 创建SQL操作对象
     * @param $table_name 单一表名，也可以是一个left join表的集合SQL
     */
    public function __construct($table_name)
    {

        $this->_table_name = $table_name;
    }

    /**
 * where 条件拼装,可以连续累加使用
 * @param $wheres  where查询条件
 * example
 * array(
 *   key1=>array(1,2,3),
 *   key2=>1
 * )
 * 代表 KEY1 IN(1,2,3)  and/or  key2=1
 * @param string $where_method where条件间的方式是and 还是or
 * @return 当前操作对象
 * @author fuyuan.shuai
 */
    public function where($wheres,$where_method="and")
    {
        if (is_array($wheres) && !empty($wheres))
        {
            $this->_where[] = $this->_spell_where_sql($wheres,$where_method);
        }
        return$this;
    }

    /**
     * 废弃 ，暂不启用
     * compare 比较条件拼装,可以连续累加使用
     * @param $compares  参与比较的规则MAP
     * array(
     *   array("key1",array(">1","<=1000"),"and"),
     *   array("key2",">20"),
     *   array("key3",array(">2015-1-1 14:00:00","<2014-12-12 15:00:00"),"or")
     * )
     * 实际为 (key1>1 and key1<=1000)  and|or  key2>20  and|or  (key3>'2015-1-1 14:00:00'  or key3 <'2014-12-12 15:00:00')
     * @param string $compare_method 比较条件间的方式是and 还是or
     * @return 当前操作对象
     * @author fuyuan.shuai
     * @example
     */
    public function compare($compares,$compare_method="and")
    {
        if (is_array($compares) && !empty($compares))
        {
            $this->_compare[] = $this->_spell_compare_sql($compares,$compare_method);
        }
        return$this;
    }
//,废弃 ，暂不启用
    private function _spell_compare_sql($compares,$compare_method)
    {
        $compare_arr = array();
        foreach ($compares as $compare)
        {
            if (is_array($compare))
            {
//                var_dump($compare);
                $compare_key = $compare[0];
                $compare_question = $compare[1];
                $compare_key_method = empty($compare[2])?"and":$compare[2];
                if (is_array($compare_question))
                {
                    $compare_key_arr=array();
                    foreach ($compare_question as $question)
                    {
//                        var_dump($question);
                        $question = $this->_build_num_compare_value($question);
//                        var_dump($question);
                        if (isset($question[0]) && isset($question[1]))
                        {
                            $compare_key_arr[] =  " {$compare_key} {$question[0]} {$question[1]} ";
                        }
                    }
                    if (is_array($compare_key_arr) && !empty($compare_key_arr))
                    {
                        $compare_key_sql = ' ('.implode($compare_key_method,$compare_key_arr).') ';
                    }else{
                        $compare_key_sql = '';
                    }
                    unset($compare_key_arr);
                    $compare_arr[] = $compare_key_sql;
                }else{
                    $question = $this->_build_num_compare_value($compare_question);

                    if (isset($question[0]) && isset($question[1]))
                    {
                        $compare_arr[] = $compare_key . " {$question[0]} {$question[1]} ";
                    }
                }
            }
        }

        if (is_array($compare_arr) && !empty($compare_arr))
        {
            $compare_sql = ' ('.implode($compare_method,$compare_arr).') ';
        }else{
            $compare_sql = '';
        }


        unset($compare_arr);
        return $compare_sql;
    }
//,废弃 ，暂不启用
    private function _build_num_compare_value($compare)
    {
        $arr = array();
        preg_match("/([<>=]+)([0-9\.\-\s\:\'\"]+)/is",$compare,$arr);
        if (isset($arr[1]) && isset($arr[2]))
        {
            return array(
                trim($arr[1]),
                trim($arr[2])
            );
        }
    }


    /**
     * 自定义where 条件拼装,可以连续累加使用
     * @param $wheres  where查询条件
     * example
     * array(
     *  " key1=1 and key2=2",
     *   "key3=3"
     * )
     * 代表 (KEY1 =1 and key2=2)  and/or  key3=3
     * @param string $where_method where条件间的方式是and 还是or
     * @return 当前操作对象
     * @author fuyuan.shuai
     */
    public function custom_where($wheres,$where_method="and")
    {
        if (is_array($wheres) && !empty($wheres))
        {
            $this->_custom_where[] = $this->_spell_custom_where_sql($wheres, $where_method);
        }
        return$this;
    }
    /**
     * like 条件拼装,可以连续累加使用
     * @param $likes  like查询条件
     * example
     * array(
     *   key1=>array("1%","%2%","3%"),
     *   key2=>1
     * )
     * 代表 (KEY1 like "1%"  or   KEY1 like "%2%"  or KEY1 like "3%" ) and/or  key2 like 1
     * @param string $like_method like条件间的方式是and 还是or
     * @return 当前操作对象
     * @author fuyuan.shuai
     */
    public function like($likes,$like_method="and")
    {
        if (is_array($likes) && !empty($likes))
        {
            $this->_like[] = $this->_spell_like_sql($likes, $like_method);
        }
        return$this;
    }
    /**
     * orderby 条件拼装,以最后一次使用的值为准
     * @param $orderby  orderby查询条件
     * example
     * array(
     *   key1=>"asc",
     *   key2=>"desc"
     * )
     * 代表 order by key1 asc,key2 desc
     * @return 当前操作对象
     * @author fuyuan.shuai
     */
    public function orderby($orderby)
    {
        if (is_array($orderby) && !empty($orderby))
        {
            $this->_orderby = $orderby;
        }
        return$this;
    }

    /**
     * groupby 条件拼装，以最后一次使用的值为准
     * @param $groupby  groupby查询条件
     * example
     * array(
     *   key1,
     *   key2
     * )
     * 代表 group by key1 ,key2
     * @return 当前操作对象
     * @author fuyuan.shuai
     */
    public function groupby($groupby)
    {
        if (is_array($groupby) && !empty($groupby))
        {
            $this->_groupby = $groupby;
        }
        return$this;
    }

    private function _spell_where_sql($wheres,$method)
    {
        $where_arr = array();
        foreach ($wheres as $k => $where)
        {
            if (is_array($where))
            {
                $in_values = implode("','",$where);
                $where_arr[] = " {$k} in ('{$in_values}') ";
            }else{
                $where_arr[] = " {$k} = '{$where}' ";
            }
        }

        if (is_array($where_arr))
        {
            $where_sql = ' ('.implode($method,$where_arr).') ';
        }else{
            $where_sql = '';
        }
        unset($where_arr);
        return $where_sql;
    }

    private function _spell_custom_where_sql($custom_wheres,$method)
    {
        $where_arr = array();
        foreach ($custom_wheres as $where)
        {
                $where_arr[] = " ({$where}) ";
        }
        if (is_array($where_arr))
        {
            $where_sql = ' ('.implode($method,$where_arr).') ';
        }else{
            $where_sql = '';
        }

        unset($where_arr);
        return $where_sql;
    }

    private function _spell_like_sql($likes,$method)
    {
        $like_arr = array();
        foreach ($likes as $k => $like)
        {
            if (is_array($like))
            {
                $like_value = array();
                foreach ($like  as $v)
                {
                    $like_value[]  = " {$k} like '{$v}' ";
                }
                $like_arr[] = "(".implode(" or ",$like_value).")";
            }else{
                $like_arr[] = " {$k} like '{$like}' ";
            }
        }

        if (is_array($like_arr))
        {
            $like_sql = ' ('.implode($method,$like_arr).') ';
        }else{
            $like_sql = '';
        }
        unset($like_arr);
        return $like_sql;
    }

    public function limit($min,$count)
    {
        if (is_numeric($min) && is_numeric($count) )
        {
            $this->_limit=array($min,$count);
        }
        return $this;
    }

    private function _build_query_sql($method=self::NP_PARAMS_VALID_METHOD_QUERY)
    {
        $where_sql = '';
        $like_sql = '';
        $custom_sql = '';
        $group_sql = '';
        $orderby_sql = '';
        $compare_sql = '';
        $limit_sql = '';

        if (is_array($this->_where) && count($this->_where) > 0)
        {
            $where_sql = implode('and',$this->_where);
        }

        if (is_array($this->_like) && count($this->_like) > 0)
        {
            $like_sql = implode('and',$this->_like);
        }

        if (is_array($this->_custom_where) && count($this->_custom_where) > 0)
        {
            $custom_sql = implode('and',$this->_custom_where);
        }

        if (is_array($this->_compare) && count($this->_compare) > 0)
        {
            $compare_sql = implode('and',$this->_compare);
        }

        if (is_array($this->_groupby) && count($this->_groupby) > 0)
        {
            $group_sql = " group by ".implode(',',$this->_groupby);
        }

        if (is_array($this->_orderby) && count($this->_orderby) > 0)
        {
            $orderby_sql = " order by ";
           foreach ($this->_orderby as $k=>$v)
           {
               $v = empty($v)?"desc":$v;
               $orderby_sql .=" {$k} {$v},";
           }
            $orderby_sql  =rtrim($orderby_sql,",");
        }
        if (is_array($this->_limit) && count($this->_limit) > 0)
        {
            $limit_sql = " limit {$this->_limit[0]},{$this->_limit[1]}";
        }

        $ex_sql = "";
        if ($where_sql||$like_sql||$custom_sql|| $compare_sql )
        {
            $where_arr=array($where_sql,$like_sql,$custom_sql,$compare_sql);
            $where_arr = array_filter($where_arr);
            $ex_sql = " where ".implode(" and ",$where_arr);
        }

        if ($method===self::NP_PARAMS_VALID_METHOD_QUERY)
        {
            $ex_sql .= " {$group_sql} {$orderby_sql} {$limit_sql}";
        }


        return $ex_sql;
    }

    public function reset()
    {
       $this->_where = array();

        $this->_orderby=array();

        $this->_like=array();

        $this->_groupby = array();

        $this->_custom_where = array();

//        $this->_table_name="";

        $this->_limit=array();

        $this->_compare = array();
    }

    /**
     * 返回count的SQL字符串
     *
     */
    public function count()
    {
        $sql = "select count(1) as count from ".$this->_table_name;
        $ex_sql =$this->_build_query_sql();
        $sql .=$ex_sql;
        $this->reset();
        return $sql;
    }

    /**
     * 返回QUERY的SQL字符串
     *
     */
    public function query($fields="*")
    {
        if (is_array($fields))
        {
            $fields_sql = implode(",",$fields);
        }else{
            $fields_sql =$fields;
        }
        unset($fields);
        $sql = "select {$fields_sql} from ".$this->_table_name;
        $ex_sql =$this->_build_query_sql();
        $sql .=$ex_sql;
        $this->reset();
        return $sql;
    }

    /**
     * 返回DELETE的SQL字符串
     *
     */
    public function delete()
    {

        $sql = "delete from ".$this->_table_name;
        $ex_sql =$this->_build_query_sql(self::NP_PARAMS_VALID_METHOD_INSERT);
        $sql .=$ex_sql;
        $this->reset();
        return $sql;
    }

    /**
 * 返回insert的SQL字符串
 *
 */
    public function insert($params)
    {
        if (!is_array($params))
        {
            return "";
        }
        $sql = "insert into ".$this->_table_name;
        $keys = array();
        $values = array();

        foreach ($params as $key=>$value)
        {
            $keys[] = $key;
            $values[] = $value;
        }

        $key_sql = implode(",",$keys);
        $value_sql = "'".implode("','",$values)."'";

        $sql .= " ({$key_sql}) values ({$value_sql})";
        $sql = str_replace("'".self::NP_PARAMS_NULL."'", 'NULL', $sql);
        $this->reset();
        return $sql;
    }

    public function m_insert($params)
    {
        if (!is_array($params))
        {
            return "";
        }
        $sql = "insert into ".$this->_table_name;
        $keys = array();
        $values = array();
        $value_sqls =array();
        foreach ($params as $num =>$item)
        {
            foreach ($item as $key=>$value)
            {
                if ($num == 0)
                {
                   $keys[] = $key; 
                }
                $values[] = $value;
            }

            
            $value_sql = "('".implode("','",$values)."')";
            $value_sqls[] = $value_sql;
        }

        $value_sql_str = implode(",", $value_sqls);

        $key_sql = implode(",",$keys);
        $sql .= " ({$key_sql}) values {$value_sql_str}";
        $sql = str_replace("'".self::NP_PARAMS_NULL."'", 'NULL', $sql);
        $this->reset();
        return $sql;
    }


    public function m_replace($params)
    {
        if (!is_array($params))
        {
            return "";
        }
        $sql = "replace into ".$this->_table_name;
        $keys = array();
        $value_sqls =array();
        foreach ($params as $num =>$item)
        {
            $values = array();
            foreach ($item as $key=>$value)
            {
                if ($num == 0)
                {
                   $keys[] = $key; 
                }
                $values[] = $value;
            }

            
            $value_sql = "('".implode("','",$values)."')";
            $value_sqls[] = $value_sql;
        }

        $value_sql_str = implode(",", $value_sqls);

        $key_sql = implode(",",$keys);
        $sql .= " ({$key_sql}) values {$value_sql_str}";
        $sql = str_replace("'".self::NP_PARAMS_NULL."'", 'NULL', $sql);
        $this->reset();
        return $sql;
    }

    /**
     * 返回insert的SQL字符串
     *
     */
    public function replace($params)
    {
        if (!is_array($params))
        {
            return "";
        }
        $sql = "replace into ".$this->_table_name;
        $keys = array();
        $values = array();

        foreach ($params as $key=>$value)
        {
            $keys[] = $key;
            $values[] = $value;
        }

        $key_sql = implode(",",$keys);
        $value_sql = "'".implode("','",$values)."'";

        $sql .= " ({$key_sql}) values ({$value_sql})";

       $sql = str_replace("'".self::NP_PARAMS_NULL."'", 'NULL', $sql);
        $this->reset();
        return $sql;
    }

    /**
     * 返回update的SQL字符串
     *
     */
    public function update($params)
    {
        if (!is_array($params))
        {
            return "";
        }
        $sql = "update ".$this->_table_name." set ";
        $keys = array();

        foreach ($params as $key=>$value)
        {
            $keys[] = " {$key}='{$value}' ";
        }

        $key_sql = implode(",",$keys);

        $sql .= " {$key_sql} ";

        $ex_sql =$this->_build_query_sql(self::NP_PARAMS_VALID_METHOD_MODIFY);
        $sql .=$ex_sql;
        $this->reset();
        return $sql;
    }

    /**
     * 检查当前注入参数是否合法
     * @param $dc  DC对象
     * @param $params  注入参数
     * @param $method 检查方式，
     * insert为插入检查，要验证数据写入的完整性
     * update 为修改检查，只验证是否存在范围外的数据，并验证数据是否合符数据库规范
     * query 为查询模式，只验证是否存在范围外的数据
     * @return array|bool  返回FALSE 代表参数检查失败，返回array 代表经过筛选后的参数组
     */
    public  function params_valid_check($dc,$params,$method=self::NP_PARAMS_VALID_METHOD_QUERY)
    {
        if (!is_array($params) || empty($params))
        {
            return $params;
        }

//        数据库包含nns_前缀，兼容没有传递前缀的传参方式
        $r_params = array();
        foreach ($params as $k=>$v)
        {
            if (strpos($k,"nns_")!==0)
            {
                $r_params["nns_".$k] = $v;
            }else{
                $r_params[$k] = $v;
            }
        }
        $params = $r_params;
        unset($r_params);
//        先检查原缓存是否命中，没有则将表结构从数据库中查询出来
        if (isset(self::$table_field_maps[$this->_table_name]))
        {
            $re = self::$table_field_maps[$this->_table_name];
        }else{
            $sql = " show columns from ".$this->_table_name;
            $re = nl_query_by_db($sql,$dc->db());
            if (!is_array($re))
            {
                $this->errors[] = $this->_table_name . " params_valid_check is execute fail! sql is {$sql}";
//                nl_log_v2_error();
                return FALSE;
            }
            self::$table_field_maps[$this->_table_name] = $re;
        }

        $return_params = array();
//      对参数做检查 并去掉不存在的字段
        foreach ($re  as $field)
        {
            $field_name = $field["Field"];
            $field_must = $field["Null"]==="NO"?TRUE:FALSE;
            $field_default = $field["Default"];
            $field_type = $field["Type"];

            $insert_value = isset($params[$field_name])?$params[$field_name]:NULL;
//          当字段为必填项并且默认值为空，没有传入任何值时，检查模式为插入模式时，代表缺插入字段，直接返回失败
            if ($field_must && $field_default===NULL && $insert_value ===NULL && $method===self::NP_PARAMS_VALID_METHOD_INSERT)
            {
                $this->errors[] = $this->_table_name . " params_valid_check is execute fail! {$field_name} is empty";

                return FALSE;
            }
//          当字段为数值类型，传入的值非数字型时,检查模式为非查询模式时，直接返回失败
            if ( $insert_value !==NULL  && (strpos($field_type,"int")===0 || strpos($field_type,"float")===0) && $method!==self::NP_PARAMS_VALID_METHOD_QUERY)
            {
                if (!is_numeric($insert_value))
                {
                    $this->errors[] = $this->_table_name . " params_valid_check is execute fail! {$field_name} is not match {$field_type}";

                    return FALSE;
                }
            }


            if ($insert_value !==NULL)
            {
                //            单双引号转义
                if(is_array($insert_value))
                {
                    foreach($insert_value as &$val)
                    {
                        $val = addslashes($val);
                    }
                }
                else
                {
                    $insert_value = addslashes($insert_value);
                }
                $return_params[$field_name] = $insert_value;
            }
        }
        unset($params);
        return $return_params;
    }


    public function get_last_error()
    {
        return $this->errors[0];
    }
} 